package com.tianlan.blog.dao;

import java.util.List;

import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.transaction.annotation.Transactional;

import com.tianlan.blog.entity.SysRole;
import com.tianlan.common.page.MyPage;

public interface SysRoleDao extends JpaRepository<SysRole, Long>{
		
	@Query(value = "SELECT id, parent_id, role_name, role_key, role_level, role_status, "
			+ "description, create_time, creator, update_time, operator, del_flag "
			+ "FROM sys_role  WHERE del_flag = '0' "
			+ "AND (id = :#{#role.id} or :#{#role.id} is null) "
			+ "AND (role_name = :#{#role.roleName} or :#{#role.roleName} is null OR :#{#role.roleName} = '') "
			+ "AND (role_key = :#{#role.roleKey} or :#{#role.roleKey} is null OR :#{#role.roleKey} = '') "
			+ "AND (role_level = :#{#role.roleLevel} or :#{#role.roleLevel} is null) "
			+ "AND (role_status = :#{#role.roleStatus} or :#{#role.roleStatus} is null OR :#{#role.roleStatus} = '') "
			+ "ORDER BY id DESC LIMIT :#{#page.start},:#{#page.size}"
			,nativeQuery = true)
	List<SysRole> selectSysRoleList(@Param("role") SysRole role,@Param("page") MyPage<SysRole> page);
	
	@Query(value = "SELECT COUNT(1) "
			+ "FROM sys_role  WHERE del_flag = '0' "
			+ "AND (id = :#{#role.id} or :#{#role.id} is null) "
			+ "AND (role_name = :#{#role.roleName} or :#{#role.roleName} is null OR :#{#role.roleName} = '') "
			+ "AND (role_key = :#{#role.roleKey} or :#{#role.roleKey} is null OR :#{#role.roleKey} = '') "
			+ "AND (role_level = :#{#role.roleLevel} or :#{#role.roleLevel} is null) "
			+ "AND (role_status = :#{#role.roleStatus} or :#{#role.roleStatus} is null OR :#{#role.roleStatus} = '') "
			,nativeQuery = true)
	int selectSysRoleCount(@Param("role") SysRole role);
	
	@Query(value = "SELECT r.* FROM sys_role r WHERE r.del_flag = '0' AND r.id in (:roleIds)", nativeQuery = true)
	List<SysRole> getSysRoleList(@Param("roleIds") List<Integer> roleIds);
		
	@Query(value = "SELECT COUNT(1) FROM sys_role WHERE role_name = ?1 OR role_Key = ?2 AND del_flag = '0' ",nativeQuery = true)
	int isSysRole(String roleName,String roleKey);
	
	@Query(value = "SELECT COUNT(1) FROM sys_role WHERE id = ?1 AND del_flag = '0' ",nativeQuery = true)
	int isRoleById(Integer roleId);
	
	@Transactional
	@Modifying
	@Query(value = "UPDATE sys_role SET role_status = ?2 WHERE id = ?1",nativeQuery = true)
	int updateRoleById(Integer id,String roleStatus);
	
	@Transactional
	@Modifying
	@Query(value = "UPDATE sys_role SET del_flag = '1' WHERE id = ?1",nativeQuery = true)
	int deleteRoleById(Integer id);
	
	long count(Specification<SysRole> specification);
}
